Generated code - Calling a database function
Preface
Calling a database function to perform additional logic onto a field and/or set of values is an advanced feature which can be useful in
e.g. filters and
in DynamicList fetches, but also in TypedViews and TypedLists. A function call is implemented in the form of a
DbFunctionCall object. This object implements the
interface
IDbFunctionCall and also
IExpression, so it can be added to an existing
Expression object and also be set to a field's
ExpressionToApply property.
This section describes the details about a database function call, what you can do with it, and illustrates the feature with an example.
On some databases, the difference between a stored procedure and a function is non-existent. The database function meant here is a function which is either a
system function, like GETDATE() on SQL Server, or a user defined function in a schema and is not directly callable from the outside through ADO.NET unless a special
crafted SQL statement is used. Rule of thumb: if you need an explicit EXEC statement to execute the function, it's not callable by this mechanism: the function
has to be callable from within a SELECT statement for example.
When
you use Linq or QuerySpec, function calls are not directly defined by you,
but specified indirectly by using .NET methods which are mapped onto SQL
functions through function mappings. The code below is part of our
lower-level API.
Definition and scope
A database function call in the form of a
DbFunctionCall object
contains hardcoded the catalog, schema and function name. The catalog/schema
names can be left empty, in which case the default catalog and schema as
defined in the connection string will be used by the RDBMS to find the
function called. So, in general only leave catalog and/or schema name empty
if you're calling a system function. The names specified for catalog/schema
are renamed in runtime catalog/schema name overwriting, if catalog/schema
name overwriting is applied at runtime to the generated code.
The function can receive any number of parameters, and they can be of type: value (any value), an Entity field object and
Expression (which implies function call,
as
DbFunctionCall is an
Expression). These parameters are passed in as
an object[] array, and for functions without parameters, you can pass null / Nothing.. If NULL has to be passed as a parameter, pass either DBNull.Value or
null/Nothing. The order in which the parameters are specified in the object[] array is also the order in which they'll be emitted into the actual function call.
Specifying constants for function parameters
It sometimes is necessary to specify constants for a function call, and these constants can't be specified with a parameter. An example is the function CAST or CONVERT.
To do this, you have to specify the function name with {
n} placeholders. When there are {
n} placeholders in the function name, LLBLGen Pro will use the function name as-is and will replace the placeholders with the parameters specified, otherwise the function name is build using the name specified plus a suffix with the parameters (e.g. MONTH(
field) ). See for the normal specification of a function name the Examples at the bottom of this section.
The following example shows how to specify a call to CAST with a constant.
// C#
ResultsetFields fields = new ResultsetFields( 2 );
fields.DefineField( OrderFieldIndex.OrderId, 0, "OrderID" );
fields.DefineField( OrderFieldIndex.OrderDate, 1, "Month" );
fields[1].ExpressionToApply = new DbFunctionCall("CAST({0} AS bigint)",
new object[] { new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) });
' VB.NET
Dim fields As New ResultsetFields(2)
fields.DefineField( OrderFieldIndex.OrderId, 0, "OrderID" )
fields.DefineField( OrderFieldIndex.OrderDate, 1, "Month" )
fields(1).ExpressionToApply = New DbFunctionCall("CAST({0} AS bigint)", _
New Object() { New DbFunctionCall("MONTH", New Object() { OrderFields.OrderDate }) })
CASE support
Although it's directly a function, we could use the function call with constant feature to specify CASE fragments for a select. Below is an example how to do that.
// C#
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(SupplierFields.Id, 0);
fields.DefineField(SupplierFields.Region, 1);
fields[1].ExpressionToApply = new DbFunctionCall(
"CASE {0} WHEN 'MA' THEN 'Massachusetts' WHEN 'OR' THEN 'Oregon' ELSE 'Unknown' END",
new object[] { SupplierFields.Region });
' VB.NET
Dim fields As New ResultsetFields(2)
fields.DefineField(SupplierFields.Id, 0)
fields.DefineField(SupplierFields.Region, 1)
fields(1).ExpressionToApply = New DbFunctionCall( _
"CASE {0} WHEN 'MA' THEN 'Massachusetts' WHEN 'OR' THEN 'Oregon' ELSE 'Unknown' END", _
New Object() { SupplierFields.Region })
Function calls in expressions.
DbFunctionCall implements
IExpression, so you can add it to any Expression object as an operand to participate in an expression.
DbFunctionCall returns
itself as the left operand inside an
ExpressionElement and type is set to
FunctionCall. The examples later on in this section will illustrate the usage of
DBFunctionCall in Expressions.
DbFunctionCall objects are ignored in in-memory filtering.
All supported databases support function calls, and you can call any function offered by the target database system. This thus means that you can use system functions
like GETDATE or DATEVAL on SQL Server, and for example VB functions on MS Access, as these are available to you in SQL.
It's a powerful feature, but also comes with a restriction: as the function name is hardcoded as a string in your code, and the order in which the parameters
have to be specified is tied to the order of the parameters in your function definition in the database system, it means that changes to the function's parameter
order or the name of the function can lead to errors at runtime. Be aware that this can happen, so document where you use which database function, so you can always
find back usage of a database function in your code if the database function changes.
Using a function call in a predicate
DbFunctionCall objects are expressions, so you can use them in predicates as well.
Take for example the
DbFunctionCall used in the first example in this section. It can be used in a predicate to filter on orders in a given month:
SelfServicing
// C#
// create a filter which filters on month equals 3
IPredicate monthFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
new object[] { new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) })) == 3;
' VB.NET
' create a filter which filters on month equals 3
Dim monthFilter As IPredicate = New EntityField("OrderMonth", New DbFunctionCall("CAST({0} AS bigint)", _
New Object() { New DbFunctionCall("MONTH", New Object() { OrderFields.OrderDate }) })) = 3
Adapter
// C#
// create a filter which filters on month equals 3
IPredicate monthFilter = new EntityField2("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
new object[] { new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) })) == 3;
' VB.NET
' create a filter which filters on month equals 3
Dim monthFilter As IPredicate = New EntityField2("OrderMonth", New DbFunctionCall("CAST({0} AS bigint)", _
New Object() { New DbFunctionCall("MONTH", New Object() { OrderFields.OrderDate }) })) = 3
Examples
Below is an example given for both Selfservicing and adapter, which fetches a set of order objects based on a filter which uses a function call.
The database function used is a user defined function created for this example, defined in the Northwind database on
SQL Server. If you're unfamiliar with
T-SQL syntaxis, the function,
fn_CalculateOrderTotal accepts two parameters, @orderID and @useDiscounts, and based on @useDiscounts' value (0 or 1),
it calculates the order total for the order with the orderID in the parameter @orderID.
-- T-SQL
CREATE FUNCTION fn_CalculateOrderTotal(@orderID int, @useDiscounts bit)
RETURNS DECIMAL
AS
BEGIN
DECLARE @toReturn DECIMAL
IF @useDiscounts=0
BEGIN
SELECT @toReturn = SUM(UnitPrice * Quantity)
FROM [Order Details]
WHERE OrderID = @orderID
GROUP BY OrderID
END
IF @useDiscounts=1
BEGIN
SELECT @toReturn = SUM((UnitPrice-Discount) * Quantity)
FROM [Order Details]
WHERE OrderID = @orderID
GROUP BY OrderID
END
RETURN @toReturn
END
The example code below fetches a single row of data all using a function call, using a DynamicList. Assert statements have been left in the code to
illustrate the values to expect for the various fields fetched.
Selfservicing
// C#
ResultsetFields fields = new ResultsetFields( 8 );
fields.DefineField( OrderFields.OrderId, 0, "OrderID" );
fields.DefineField( OrderFields.OrderDate, 1, "Month" );
fields.DefineField( OrderFields.OrderDate, 2, "Year" );
fields.DefineField( OrderFields.OrderDate, 3, "YearPlus4" );
fields.DefineField( OrderFields.OrderDate, 4, "OrderTotalWithDiscounts" );
fields.DefineField( OrderFields.OrderDate, 5, "OrderTotalWithoutDiscounts" );
fields.DefineField( OrderFields.OrderDate, 6, "YearOfGetDate" );
fields.DefineField( OrderFields.OrderDate, 7, "RealOrderDate" );
fields[1].ExpressionToApply = new DbFunctionCall( "MONTH", new object[] { OrderFields.OrderDate } );
fields[2].ExpressionToApply = new DbFunctionCall( "YEAR", new object[] { OrderFields.OrderDate } );
fields[3].ExpressionToApply = new Expression( new DbFunctionCall( "YEAR", new object[] { OrderFields.OrderDate } ), ExOp.Add, 4 );
fields[4].ExpressionToApply = new DbFunctionCall( "dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 1 } );
fields[5].ExpressionToApply = new DbFunctionCall( "dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 0 } );
fields[6].ExpressionToApply = new DbFunctionCall( "YEAR", new object[] { new DbFunctionCall( "GETDATE", null ) } );
DataTable results = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable( fields, results, 0, null, null, null, true, null, null, 0, 0 );
foreach( DataRow row in results.Rows )
{
DateTime realOrderDate = (DateTime)row[7];
Assert.AreEqual( (int)row[1], realOrderDate.Month );
Assert.AreEqual( (int)row[2], realOrderDate.Year );
Assert.AreEqual( (int)row[3], realOrderDate.Year + 4 );
Assert.AreEqual( (int)row[6], DateTime.Now.Year );
}
' VB.NET
Dim fields As New ResultsetFields( 8 )
fields.DefineField( OrderFields.OrderId, 0, "OrderID" )
fields.DefineField( OrderFields.OrderDate, 1, "Month" )
fields.DefineField( OrderFields.OrderDate, 2, "Year" )
fields.DefineField( OrderFields.OrderDate, 3, "YearPlus4" )
fields.DefineField( OrderFields.OrderDate, 4, "OrderTotalWithDiscounts" )
fields.DefineField( OrderFields.OrderDate, 5, "OrderTotalWithoutDiscounts" )
fields.DefineField( OrderFields.OrderDate, 6, "YearOfGetDate" )
fields.DefineField( OrderFields.OrderDate, 7, "RealOrderDate" )
fields(1).ExpressionToApply = New DbFunctionCall( "MONTH", New Object() { OrderFields.OrderDate } )
fields(2).ExpressionToApply = New DbFunctionCall( "YEAR", New Object() { OrderFields.OrderDate } )
fields(3).ExpressionToApply = New Expression( New DbFunctionCall( "YEAR", New Object() { OrderFields.OrderDate } ), ExOp.Add, 4 )
fields(4).ExpressionToApply = New DbFunctionCall( "dbo", "fn_CalculateOrderTotal", New Object() { OrderFields.OrderId, 1 } )
fields(5).ExpressionToApply = New DbFunctionCall( "dbo", "fn_CalculateOrderTotal", New Object() { OrderFields.OrderId, 0 } )
fields(6).ExpressionToApply = New DbFunctionCall( "YEAR", New Object() { New DbFunctionCall( "GETDATE", Nothing ) } )
Dim results As New DataTable()
Dim dao As New TypedListDAO()
dao.GetMultiAsDataTable( fields, results, 0, Nothing, Nothing, Nothing, True, Nothing, Nothing, 0, 0 )
For Each row As DataRow in results.Rows
Dim realOrderDate As DateTime = CType(row(7), DateTime)
Assert.AreEqual( CInt(row(1)), realOrderDate.Month )
Assert.AreEqual( CInt(row(2)), realOrderDate.Year )
Assert.AreEqual( CInt(row(3)), realOrderDate.Year + 4 )
Assert.AreEqual( CInt(row(6)), DateTime.Now.Year )
Next
Adapter
// C#
ResultsetFields fields = new ResultsetFields( 8 );
fields.DefineField( OrderFields.OrderId, 0, "OrderID" );
fields.DefineField( OrderFields.OrderDate, 1, "Month" );
fields.DefineField( OrderFields.OrderDate, 2, "Year" );
fields.DefineField( OrderFields.OrderDate, 3, "YearPlus4" );
fields.DefineField( OrderFields.OrderDate, 4, "OrderTotalWithDiscounts" );
fields.DefineField( OrderFields.OrderDate, 5, "OrderTotalWithoutDiscounts" );
fields.DefineField( OrderFields.OrderDate, 6, "YearOfGetDate" );
fields.DefineField( OrderFields.OrderDate, 7, "RealOrderDate" );
fields[1].ExpressionToApply = new DbFunctionCall( "MONTH", new object[] { OrderFields.OrderDate } );
fields[2].ExpressionToApply = new DbFunctionCall( "YEAR", new object[] { OrderFields.OrderDate } );
fields[3].ExpressionToApply = new Expression( new DbFunctionCall( "YEAR", new object[] { OrderFields.OrderDate } ), ExOp.Add, 4 );
fields[4].ExpressionToApply = new DbFunctionCall( "dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 1 } );
fields[5].ExpressionToApply = new DbFunctionCall( "dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 0 } );
fields[6].ExpressionToApply = new DbFunctionCall( "YEAR", new object[] { new DbFunctionCall( "GETDATE", null ) } );
DataTable results = new DataTable();
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
adapter.FetchTypedList( fields, results, null );
}
foreach( DataRow row in results.Rows )
{
DateTime realOrderDate = (DateTime)row[7];
Assert.AreEqual( (int)row[1], realOrderDate.Month );
Assert.AreEqual( (int)row[2], realOrderDate.Year );
Assert.AreEqual( (int)row[3], realOrderDate.Year + 4 );
Assert.AreEqual( (int)row[6], DateTime.Now.Year );
}
' VB.NET
Dim fields As New ResultsetFields( 8 )
fields.DefineField( OrderFields.OrderId, 0, "OrderID" )
fields.DefineField( OrderFields.OrderDate, 1, "Month" )
fields.DefineField( OrderFields.OrderDate, 2, "Year" )
fields.DefineField( OrderFields.OrderDate, 3, "YearPlus4" )
fields.DefineField( OrderFields.OrderDate, 4, "OrderTotalWithDiscounts" )
fields.DefineField( OrderFields.OrderDate, 5, "OrderTotalWithoutDiscounts" )
fields.DefineField( OrderFields.OrderDate, 6, "YearOfGetDate" )
fields.DefineField( OrderFields.OrderDate, 7, "RealOrderDate" )
fields(1).ExpressionToApply = New DbFunctionCall( "MONTH", New Object() { OrderFields.OrderDate } )
fields(2).ExpressionToApply = New DbFunctionCall( "YEAR", New Object() { OrderFields.OrderDate } )
fields(3).ExpressionToApply = New Expression( New DbFunctionCall( "YEAR", New Object() { OrderFields.OrderDate } ), ExOp.Add, 4 )
fields(4).ExpressionToApply = New DbFunctionCall( "dbo", "fn_CalculateOrderTotal", New Object() { OrderFields.OrderId, 1 } )
fields(5).ExpressionToApply = New DbFunctionCall( "dbo", "fn_CalculateOrderTotal", New Object() { OrderFields.OrderId, 0 } )
fields(6).ExpressionToApply = New DbFunctionCall( "YEAR", New Object() { New DbFunctionCall( "GETDATE", Nothing ) } )
Dim results As New DataTable()
Dim adapter As New DataAccessAdapter()
Try
adapter.FetchTypedList( fields, results, Nothing )
Finally
adapter.Dispose()
End Try
For Each row As DataRow in results.Rows
Dim realOrderDate As DateTime = CType(row(7), DateTime)
Assert.AreEqual( CInt(row(1)), realOrderDate.Month )
Assert.AreEqual( CInt(row(2)), realOrderDate.Year )
Assert.AreEqual( CInt(row(3)), realOrderDate.Year + 4 )
Assert.AreEqual( CInt(row(6)), DateTime.Now.Year )
Next